﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DataAccessLayer;
using System.Data.Common;

public partial class webadmin_pages_import_data : System.Web.UI.Page
{
    public Common comm = new Common();
    DataRow catdtr;
    public string act = "";
    public string id = "",mess="";
    string sTemp;
    public string messageError;

    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnOk_Click(object sender, EventArgs e)
    {
        try
        {
            string tep_tin = "";
			tep_tin=SaveFile(txtfile);
			
			if(tep_tin!=""){
				string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"+
					" Data Source="+tep_tin+";Extended Properties="+
					" Excel 8.0;HDR=YES;";
				messageError=connectionString;
				string str = "PROVIDER=SQLOLEDB;DATA SOURCE=(local);INITIAL CATALOG=pubs;";
				string sql = "";
				DbProviderFactory factory =
				  DbProviderFactories.GetFactory("System.Data.OleDb");

				using (DbConnection connection = factory.CreateConnection())
				{
					connection.ConnectionString = connectionString;

					using (DbCommand command = connection.CreateCommand())
					{
						// Cities$ comes from the name of the worksheet
						command.CommandText = "SELECT name,nick,notes FROM [ho_tro_truc_tuyen$]";

						connection.Open();

						using (DbDataReader dr = command.ExecuteReader())
						{
							sql = "";
							while (dr.Read())
							{
								messageError+=dr["name"].ToString();
								sql = "Insert HeThong_ho_tro_truc_tuyen(name,nick,notes)" +
									"VALUES  (" +
									"N'" + dr["name"].ToString() + "',N'" + 
									dr["nick"].ToString() + "',N'" + dr["notes"].ToString() + "'" +
									")";
								comm.execNoneQuery(sql);
							}
						}
					}
				}
			}
        }
        catch (Exception err)
        {
            messageError += err.Message.ToString();
        }

    }

    public string SaveFile(FileUpload file)
    {
        try
        {
            Boolean fileOk=false;
            if ((file.PostedFile != null) && (file.PostedFile.InputStream.Length > 0))
            {
                String[] allowedExtensions = null;
                String fileExtension =
                    System.IO.Path.GetExtension(file.FileName).ToLower();
                string path = "../../Uploads/";
                
                if (fileExtension == ".xls")
                {
                    fileOk = true;
                }

                if (fileOk)
                {
                    try
                    {
                        file.PostedFile.SaveAs(Server.MapPath(path)
                            + file.FileName);
                        return (Server.MapPath(path) + file.FileName);
                    }
                    catch (Exception ex)
                    {
                        messageError = "Không thể upload file." + ex.Message;
                        return "";
                    }
                }
                else
                {
                    messageError = "Định dạng file không đúng qui định của mục tin tức này";
                    return "";
                }
            }
            else
                //mess = "Chưa chọn file để upload hoặc file này không tồn tại!";
                return "";
        }
        catch (Exception e)
        {
            messageError = e.Message;
            return "";
        }
    }
}
